import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Scanner;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;

public class Studentsystem {
    private static ArrayList<Student> studentList = new ArrayList<>();

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        loadStudentsFromDB();

        Scanner sc = new Scanner(System.in);
        while (true) {
            System.out.println("--------Welcome to student management--------");
            System.out.println("1 添加学生");
            System.out.println("2 删除学生");
            System.out.println("3 修改学生");
            System.out.println("4 查看所有学生");
            System.out.println("5 导入学生数据(.txt/.csv/.xlsx)");
            System.out.println("6 导出学生数据(.xlsx)");
            System.out.println("7 退出");
            System.out.print("请输入您的选择：");

            String choice = sc.nextLine();

            switch (choice) {
                case "1": add(sc); break;
                case "2": delete(sc); break;
                case "3": update(sc); break;
                case "4": listAll(); break;
                case "5": importFromFile(sc); break;
                case "6": exportToExcel(sc); break;
                case "7":
                    System.out.println("Thank you!!!");
                    System.exit(0);
                default:
                    System.out.println("您的输入有误，请重新输入");
            }
        }
    }

    public static void exportToExcel(Scanner sc) {
        // 重新从数据库加载最新数据
        try {
            loadStudentsFromDB();
        } catch (SQLException e) {
            System.out.println("加载学生数据失败: " + e.getMessage());
            return;
        }

        System.out.println("请输入要导出的文件路径(例如: students_export.xlsx):");
        String filePath = sc.nextLine().trim();

        if (!filePath.toLowerCase().endsWith(".xlsx")) {
            filePath += ".xlsx";
        }

        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            XSSFSheet sheet = workbook.createSheet("学生数据");

            XSSFRow headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("学号");
            headerRow.createCell(1).setCellValue("姓名");
            headerRow.createCell(2).setCellValue("年龄");
            headerRow.createCell(3).setCellValue("地址");

            int rowNum = 1;
            for (Student student : studentList) {
                XSSFRow row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(student.getId());
                row.createCell(1).setCellValue(AESUtil.decrypt(student.getName()));
                row.createCell(2).setCellValue(student.getAge());
                row.createCell(3).setCellValue(AESUtil.decrypt(student.getAddress()));
            }

            for (int i = 0; i < 4; i++) {
                sheet.autoSizeColumn(i);
            }

            try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
                workbook.write(outputStream);
                System.out.println("导出成功! 文件已保存到: " + new File(filePath).getAbsolutePath());
            } catch (IOException e) {
                System.out.println("文件写入错误: " + e.getMessage());
            }
        } catch (IOException e) {
            System.out.println("创建Excel文件错误: " + e.getMessage());
        }
    }

    public static void importFromFile(Scanner sc) throws SQLException {
        System.out.println("请输入要导入的文件路径(支持.txt/.csv/.xlsx文件):");
        String filePath = sc.nextLine().trim();

        if (filePath.toLowerCase().endsWith(".xlsx")) {
            importFromExcel(filePath);
        } else {
            importFromTextFile(filePath);
        }
    }

    private static void importFromTextFile(String filePath) throws SQLException {
        try {
            java.util.List<String> lines = Files.readAllLines(Paths.get(filePath));
            int successCount = 0;
            int failCount = 0;

            for (String line : lines) {
                if (line.trim().isEmpty()) continue;

                String[] parts = line.split(",|\\t|\\s+");

                if (parts.length < 4) {
                    System.out.println("数据格式错误，跳过: " + line);
                    failCount++;
                    continue;
                }

                String id = parts[0].trim();
                String name = parts[1].trim();
                int age;
                try {
                    age = Integer.parseInt(parts[2].trim());
                } catch (NumberFormatException e) {
                    System.out.println("年龄格式错误，跳过: " + line);
                    failCount++;
                    continue;
                }
                String address = parts[3].trim();

                String encryptedName = AESUtil.encrypt(name);
                String encryptedAddress = AESUtil.encrypt(address);

                if (!processStudentRecord(id, encryptedName, age, encryptedAddress)) {
                    failCount++;
                } else {
                    successCount++;
                }
            }

            System.out.println("导入完成! 成功: " + successCount + "条, 失败: " + failCount + "条");
        } catch (IOException e) {
            System.out.println("文件读取错误: " + e.getMessage());
        }
    }

    private static void importFromExcel(String filePath) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = stu_database.getConnection();
            conn.setAutoCommit(false);

            String sql = "INSERT INTO students(id, name, age, address) VALUES(?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            try (FileInputStream fis = new FileInputStream(filePath);
                 Workbook workbook = new XSSFWorkbook(fis)) {

                Sheet sheet = workbook.getSheetAt(0);
                int successCount = 0;
                int failCount = 0;
                boolean firstRow = true;

                for (Row row : sheet) {
                    if (isRowEmpty(row)) {
                        continue;
                    }

                    if (firstRow) {
                        firstRow = false;
                        if (isHeaderRow(row)) {
                            System.out.println("检测到标题行，跳过处理");
                            continue;
                        }
                    }

                    String id = getCellValue(row.getCell(0));
                    String name = getCellValue(row.getCell(1));
                    String ageStr = getCellValue(row.getCell(2));
                    String address = getCellValue(row.getCell(3));

                    if (id == null || id.isEmpty() || name == null || name.isEmpty() ||
                            ageStr == null || ageStr.isEmpty() || address == null) {
                        System.out.println("数据不完整，跳过第 " + (row.getRowNum() + 1) + " 行");
                        failCount++;
                        continue;
                    }

                    int age;
                    try {
                        age = Integer.parseInt(ageStr);
                    } catch (NumberFormatException e) {
                        System.out.println("年龄格式错误，跳过第 " + (row.getRowNum() + 1) + " 行");
                        failCount++;
                        continue;
                    }

                    if (exists(id)) {
                        System.out.println("学号已存在，跳过: " + id);
                        failCount++;
                        continue;
                    }

                    String encryptedName = AESUtil.encrypt(name);
                    String encryptedAddress = AESUtil.encrypt(address);

                    pstmt.setString(1, id);
                    pstmt.setString(2, encryptedName);
                    pstmt.setInt(3, age);
                    pstmt.setString(4, encryptedAddress);

                    pstmt.addBatch();
                    studentList.add(new Student(id, encryptedName, age, encryptedAddress));
                    successCount++;
                }

                int[] results = pstmt.executeBatch();
                conn.commit();

                int actualSuccess = 0;
                for (int result : results) {
                    if (result == Statement.EXECUTE_FAILED) {
                        failCount++;
                    } else {
                        actualSuccess++;
                    }
                }

                System.out.println("Excel导入完成! 成功: " + actualSuccess + "条, 失败: " + failCount + "条");
            } catch (IOException e) {
                System.out.println("Excel文件读取错误: " + e.getMessage());
                if (conn != null) {
                    conn.rollback();
                }
            }
        } catch (SQLException e) {
            System.out.println("数据库操作错误: " + e.getMessage());
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    private static boolean isHeaderRow(Row row) {
        String cell1 = getCellValue(row.getCell(0));
        String cell2 = getCellValue(row.getCell(1));
        String cell3 = getCellValue(row.getCell(2));
        String cell4 = getCellValue(row.getCell(3));

        return (cell1 != null && (cell1.contains("学号") || cell1.contains("ID"))) ||
                (cell2 != null && (cell2.contains("姓名") || cell2.contains("Name"))) ||
                (cell3 != null && (cell3.contains("年龄") || cell3.contains("Age"))) ||
                (cell4 != null && (cell4.contains("地址") || cell4.contains("Address")));
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    private static boolean processStudentRecord(String id, String name, int age, String address) throws SQLException {
        if (exists(id)) {
            System.out.println("学号已存在，跳过: " + id);
            return false;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = stu_database.getConnection();
            String sql = "INSERT INTO students(id, name, age, address) VALUES(?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            pstmt.setString(2, name);
            pstmt.setInt(3, age);
            pstmt.setString(4, address);

            int count = pstmt.executeUpdate();
            if (count > 0) {
                studentList.add(new Student(id, name, age, address));
                return true;
            } else {
                System.out.println("添加学生失败: " + id);
                return false;
            }
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    public static void loadStudentsFromDB() throws SQLException {
        studentList.clear();
        Connection conn = stu_database.getConnection();
        String sql = "SELECT * FROM students";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            Student s = new Student(
                    rs.getString("id"),
                    rs.getString("name"),
                    rs.getInt("age"),
                    rs.getString("address")
            );
            studentList.add(s);
        }

        stu_database.close(conn, pstmt, rs);
    }

    public static void add(Scanner sc) throws SQLException {
        System.out.println("请输入学号：");
        String id = sc.nextLine();
        if (exists(id)) {
            System.out.println("学号已存在，请重新输入");
            return;
        }

        System.out.println("请输入姓名：");
        String name = sc.nextLine();
        System.out.println("请输入年龄：");
        int age = Integer.parseInt(sc.nextLine());
        System.out.println("请输入住址：");
        String address = sc.nextLine();

        String encryptedName = AESUtil.encrypt(name);
        String encryptedAddress = AESUtil.encrypt(address);

        Connection conn = stu_database.getConnection();
        String sql = "INSERT INTO students(id, name, age, address) VALUES(?, ?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, id);
        pstmt.setString(2, encryptedName);
        pstmt.setInt(3, age);
        pstmt.setString(4, encryptedAddress);

        int count = pstmt.executeUpdate();
        if (count > 0) {
            studentList.add(new Student(id, encryptedName, age, encryptedAddress));
            System.out.println("添加学生成功");
        } else {
            System.out.println("添加学生失败");
        }

        stu_database.close(conn, pstmt, null);
    }

    public static void delete(Scanner sc) throws SQLException {
        System.out.println("请输入要删除的学生学号：");
        String id = sc.nextLine();

        if (!exists(id)) {
            System.out.println("该学号不存在，请重新输入");
            return;
        }

        Connection conn = stu_database.getConnection();
        String sql = "DELETE FROM students WHERE id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, id);

        int count = pstmt.executeUpdate();
        if (count > 0) {
            Iterator<Student> iterator = studentList.iterator();
            while (iterator.hasNext()) {
                Student s = iterator.next();
                if (s.getId().equals(id)) {
                    iterator.remove();
                    break;
                }
            }
            System.out.println("删除学生成功");
        } else {
            System.out.println("删除学生失败");
        }

        stu_database.close(conn, pstmt, null);
    }

    public static void update(Scanner sc) throws SQLException {
        System.out.println("请输入要修改的学生学号：");
        String id = sc.nextLine();

        if (!exists(id)) {
            System.out.println("该学号不存在，请重新输入");
            return;
        }

        System.out.println("请输入姓名：");
        String name = sc.nextLine();
        System.out.println("请输入年龄：");
        int age = Integer.parseInt(sc.nextLine());
        System.out.println("请输入住址：");
        String address = sc.nextLine();

        String encryptedName = AESUtil.encrypt(name);
        String encryptedAddress = AESUtil.encrypt(address);

        Connection conn = stu_database.getConnection();
        String sql = "UPDATE students SET name = ?, age = ?, address = ? WHERE id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, encryptedName);
        pstmt.setInt(2, age);
        pstmt.setString(3, encryptedAddress);
        pstmt.setString(4, id);
        int count = pstmt.executeUpdate();
        if (count > 0) {
            Iterator<Student> iterator = studentList.iterator();
            while (iterator.hasNext()) {
                Student s = iterator.next();
                if (s.getId().equals(id)) {
                    s.setName(encryptedName);
                    s.setAge(age);
                    s.setAddress(encryptedAddress);
                    break;
                }
            }
            System.out.println("修改学生成功");
        } else {
            System.out.println("修改学生失败");
        }

        stu_database.close(conn, pstmt, null);
    }

    public static void listAll() {
        if (studentList.isEmpty()) {
            System.out.println("无数据，请先添加学生信息");
        } else {
            System.out.println("学号\t\t姓名\t年龄\t住址");
            for (Student student : studentList) {
                System.out.printf("%s\t%s\t%d\t%s%n",
                        student.getId(),
                        AESUtil.decrypt(student.getName()),
                        student.getAge(),
                        AESUtil.decrypt(student.getAddress()));
            }
        }
    }

    public static boolean exists(String id) {
        Iterator<Student> iterator = studentList.iterator();
        while (iterator.hasNext()) {
            if (iterator.next().getId().equals(id)) {
                return true;
            }
        }
        return false;
    }
}